﻿using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using Microsoft.EntityFrameworkCore.Storage;
using System.Data;
using System.Linq.Expressions;

namespace QueryData
{
    public class BasicDbContext:DbContext
    {
        // 把显式编译的查询保存到私有变量
        private static Func<BasicDbContext, int, IEnumerable<BasicEntity>> _getGreatThan = EF.CompileQuery((BasicDbContext db, int id) => db.BasicEntities.Where(x => x.Id > id).ToList());

        public DbSet<BasicEntity> BasicEntities => Set<BasicEntity>();
        public DbSet<MainEntity> MainEntities => Set<MainEntity>();
        public DbSet<SubEntity> SubEntities => Set<SubEntity>();
        public DbSet<SubSubEntity> SubSubEntities => Set<SubSubEntity>();

        public BasicDbContext() { }
        public BasicDbContext(DbContextOptions options) : base(options) { }
        public BasicDbContext(DbContextOptions<BasicDbContext> options) : base(options) { }

        // 通过公共方法引用编译的查询
        public IEnumerable<BasicEntity> GetGreatThan(int id)
        {
            return _getGreatThan(this, id);
        }

        // 引用表值函数并暴露到IQueryable中
        public IQueryable<FunTableEntity> GetFunTable(int number) => FromExpression(() => GetFunTable(number));

        // 定义映射到自定义EF Core SQL翻译的方法，因此方法本身没有实现，如果需要同时支持客户端调用的话也可以提供实现
        [DbFunction]
        public static double MyFunc(double first, int second) => throw new NotImplementedException();

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);

            optionsBuilder.UseSqlServer(
        "Server=(localdb)\\mssqllocaldb;Database=QueryDataTest;Trusted_Connection=True;MultipleActiveResultSets=true");

            // 启用延迟加载代理
            //optionsBuilder.UseLazyLoadingProxies();

            // 注册命令拦截器
            //optionsBuilder.AddInterceptors(new MyCommandInterceptor());
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            // 注册表值函数的实体类型
            modelBuilder.Entity<FunTableEntity>().HasNoKey();

            // 注册表值函数，数据库中也要定义匹配的函数
            modelBuilder.HasDbFunction(() => GetFunTable(default))
                .HasName("FunTable");

            // 注册自定义翻译表达式，手动构建表达式树
            // 100 * ABS(first - second) / ((first + second) / 2)
            modelBuilder.HasDbFunction(
                    typeof(BasicDbContext).GetMethod(nameof(MyFunc)))
                .HasTranslation(
                    args =>
                        new SqlBinaryExpression(
                            ExpressionType.Multiply,
                            new SqlConstantExpression(
                                Expression.Constant(100),
                                new IntTypeMapping("int", DbType.Int32)),
                            new SqlBinaryExpression(
                                ExpressionType.Divide,
                                new SqlFunctionExpression(
                                    "ABS",
                                    new SqlExpression[]
                                    {
                                    new SqlBinaryExpression(
                                        ExpressionType.Subtract,
                                        args.First(),
                                        args.Skip(1).First(),
                                        args.First().Type,
                                        args.First().TypeMapping)
                                    },
                                    nullable: true,
                                    argumentsPropagateNullability: new[] { true, true },
                                    type: args.First().Type,
                                    typeMapping: args.First().TypeMapping),
                                new SqlBinaryExpression(
                                    ExpressionType.Divide,
                                    new SqlBinaryExpression(
                                        ExpressionType.Add,
                                        args.First(),
                                        args.Skip(1).First(),
                                        args.First().Type,
                                        args.First().TypeMapping),
                                    new SqlConstantExpression(
                                        Expression.Constant(2),
                                        new IntTypeMapping("int", DbType.Int32)),
                                    args.First().Type,
                                    args.First().TypeMapping),
                                args.First().Type,
                                args.First().TypeMapping),
                            args.First().Type,
                            args.First().TypeMapping));
        }

        // 使用特性标记方法要映射到返回标量值的数据库函数
        // 在查询中调用则会翻译为对数据库函数的调用
        // 在数据库中也要确保定义了匹配的函数
        [DbFunction]
        public static int MyFunction(int value)
        {
            return value % 2;
        }

    }
}
